WINDOW-FUNCTION-RANK

WINDOW FUNCTION RANK

Description

This function is used to represent rankings. Unlike DENSE_RANK(), it allows vacancies. For example, if there are two parallel elements that should both be ranked 1, then the third rank in DENSE_RANK() will be 2, but that in RANK() will be 3.

RANK() OVER(partition_by_clause order_by_clause)

Example

Rank by the x column

select x, y, rank() over(partition by x order by y) as rank from int_t;

| x  | y    | rank     |
|----|------|----------|
| 1  | 1    | 1        |
| 1  | 2    | 2        |
| 1  | 2    | 2        |
| 2  | 1    | 1        |
| 2  | 2    | 2        |
| 2  | 3    | 3        |
| 3  | 1    | 1        |
| 3  | 1    | 1        |
| 3  | 2    | 3        |

Keywords

WINDOW,FUNCTION,RANK